const express = require('express')
const router = express.Router()
const { DATABASEformatTime, DATESUBFORMAT } = require('../utils')
const mysql = require('mysql');
const https = require('https')
const db_vx = mysql.createConnection({
    host: '127.0.0.1',
    user: 'root',
    password: '020522',
    database: 'vx'
})
// 刷新数据库防止断连
setInterval(function () {
    db_vx.query('SELECT 1');
}, 1080000);
// 登录
router.get('/vx/user', (req, res) => {
    const openid = req.query.openid;
    if (!openid) {
        return res.send({
            code: 200,
            msg: "openid为空"
        })
    }
    const userInfo = req.query;
    const sqlstr = "SELECT * FROM users WHERE openid=?";
    const time = DATABASEformatTime(new Date())
    db_vx.query(sqlstr, [userInfo.openid], (err, result) => {
        if (err) return res.send(err)
        // 新用户首次登录
        if (result.length == 0) {
            const insertSQL = "INSERT INTO users(openid,first_login_time,last_login_time) VALUES(?,?,?)"

            db_vx.query(insertSQL, [openid, time, time], (err, result) => {
                if (err) return res.send(err)
                if (result.affectedRows != 1) {
                    res.send({
                        code: 401,
                        msg: "出现未知错误"
                    })
                }
                return res.send({
                    code: 200,
                    first: true,
                    msg: "欢迎初次登录"
                })
            })
            // 再次登录
        } else {
            const updateSQL = "UPDATE users SET last_login_time=? WHERE openid=?";
            db_vx.query(updateSQL, [time, openid])
            res.send({
                code: 200,
                msg: "请求成功",
                headshot: result.headshot
            })
        }
    })
})
// 获取用户基本信息
router.get('/vx/userinfo', (req, res) => {
    const openid = req.query.openid;
    if (!openid) {
        return res.send({
            code: 200,
            msg: "openid为空"
        })
    }
    const sql = "SELECT name,age,gender,vaccine_times,virus FROM user_info WHERE openid=?"
    db_vx.query(sql, [openid], (err, result) => {
        if (err) return res.send(err);
        if (result.length == 0) {
            return res.send({
                code: 200,
                msg: "未查询到数据"
            })
        } else {
            let userInfo = result[0];
            if (userInfo) {
                return res.send({
                    code: 200,
                    msg: "成功",
                    data: {
                        name: userInfo.name,
                        age: userInfo.age,
                        gender: userInfo.gender,
                        vaccine_times: userInfo.vaccine_times,
                        virus: userInfo.virus
                    }
                })
            } else {
                return res.send({
                    code: 400,
                    msg: "错误"
                })
            }

        }
    })
})
// 获取行程信息
router.get('/vx/userinfo/location', (req, res) => {
    //获取客户端IP
    // const IP = req.ip.split(":").reverse()[0];
    const IP = "113.78.181.221"
    const openid = req.query.openid;
    const appres = res;
    if (!openid) {
        return res.send({
            code: 200,
            msg: "openid为空"
        })
    }
    let nowData;
    // 查询SQL
    const sql = "SELECT location,staytime,firstdate,enddate,id FROM location WHERE openid=? ORDER BY enddate DESC, id DESC LIMIT 5 ";
    let url = `https://api.vore.top/api/IPdata?ip=${IP}`;
    // 查询当前位置
    const getIPANDSetLocation = https.get(url, (res) => {
        res.on("data", (data) => {
            nowData += data
        })
        res.on("end", () => {
            // 解析数据
            nowData = nowData.replace("undefined", "")
            nowData = JSON.parse(nowData)
            const insertSQL = "INSERT INTO location (openid,location,staytime,firstdate,enddate) values(?,?,?,?,?)";
            const nowLocation = `${nowData.adcode.p}-${nowData.adcode.c}`
            const nowDate = new Date();
            const formatNowDate = DATABASEformatTime(nowDate)
            let DataFromDateBase;
            db_vx.query(sql, [openid], (err, result) => {
                if (err) return appres.send(err)
                // 数据库中如果没有位置信息，从免费api获取对应地址返回给小程序客户端
                // 若成功获取地址信息
                // 比对是否与上次位置相同
                if (result.length == 0) {
                    // 添加新的位置信息
                    db_vx.query(insertSQL, [openid, nowLocation, "00:00:00", formatNowDate, formatNowDate], (err, result) => {
                        if (err) return appres.send({
                            code: 400,
                            msg: "发生错误",
                            data: err
                        })
                        if (result.affectedRows != 1) {
                            return appres.send({
                                code: 400,
                                msg: "添加位置信息失败"
                            })
                        }
                        // 初次产生的数据
                        DataFromDateBase = {
                            "location": `${nowData.adcode.p}-${nowData.adcode.c}`,
                            "staytime": '00:00:00',
                            "firstdate": nowDate,
                            "enddate": nowDate
                        }
                        // 返回数据
                        return appres.send({
                            code: 200,
                            msg: "成功,初次储存位置信息",
                            data: DataFromDateBase
                        })
                    })
                } else {
                    DataFromDateBase = result;
                    const firstTime = new Date(result[0].firstdate)
                    if (result[0].location == `${nowData.adcode.p}-${nowData.adcode.c}`) {
                        const updateSQL = "UPDATE location SET enddate=?,staytime=? WHERE id=? "
                        // 计算新的时间
                        let newStayTime = DATESUBFORMAT(nowDate - firstTime);
                        db_vx.query(updateSQL, [DATABASEformatTime(nowDate), newStayTime, result[0].id], (err, result) => {
                            // 处理错误
                            if (err) return appres.send({
                                code: 400,
                                msg: "发生错误",
                                data: err
                            })
                            if (result.affectedRows != 1) {
                                return appres.send({
                                    code: 400,
                                    msg: "未成功更新位置"
                                })
                            }
                            // 修改从数据库中获取的数据
                            DataFromDateBase[0].staytime = newStayTime;
                            DataFromDateBase[0].enddate = formatNowDate;
                            // 返回数据
                            return appres.send({
                                code: 200,
                                msg: "成功,未离开上次位置",
                                data: DataFromDateBase
                            })
                        })
                    }
                    // 位置不相同 
                    else {
                        // 添加新的位置信息
                        db_vx.query(insertSQL, [openid, nowLocation, "00:00:00", formatNowDate, formatNowDate], (err, result) => {
                            if (err) return appres.send({
                                code: 400,
                                msg: "错误",
                                data: err
                            })
                            if (result.affectedRows != 1) {
                                return appres.send({
                                    code: 400,
                                    msg: "未成功更新位置"
                                })
                            }
                            // 修改从数据库中获取的数据
                            DataFromDateBase.unshift({
                                "location": `${nowData.adcode.p}-${nowData.adcode.c}`,
                                "staytime": '00:00:00',
                                "firstdate": nowDate,
                                "enddate": nowDate
                            })
                            // 返回数据
                            return appres.send({
                                code: 200,
                                msg: "成功,到达一个新位置",
                                data: DataFromDateBase
                            })
                        })
                    }
                }
            })
        })
    })
    // 处理报错
    getIPANDSetLocation.on('error', () => {
        return appres.send({
            code: 400,
            msg: "API错误或网络问题"
        })
    })
})
// 获取疫苗信息
router.get('/vx/userinfo/vaccine', (req, res) => {
    const openid = req.query.openid;
    if (!openid) {
        return res.send({
            code: 200,
            msg: "openid为空"
        })
    }
    const SQL = "SELECT injection_date,vaccine_name FROM vaccine  WHERE openid=? ORDER BY injection_date";
    db_vx.query(SQL, openid, (err, result) => {
        if (err) return res.send({
            code: 400,
            msg: "错误",
            data: err
        })
        if (result.length == 0) {
            return res.send({
                code: 200,
                msg: '未查询到该用户的疫苗数据',
                data: null
            })
        }
        return res.send({
            code: 200,
            msg: "成功",
            data: result
        })
    })
})
// 获取核酸信息
router.get('/vx/userinfo/pcrtest', (req, res) => {
    const openid = req.query.openid;
    if (!openid) {
        return res.send({
            code: 200,
            msg: "openid为空"
        })
    }
    const SQL = "SELECT effective_time,pcr_test_loc,checked,test_date FROM pcr_test WHERE openid=? ORDER BY test_date";
    db_vx.query(SQL, openid, (err, result) => {
        if (err) return res.send({
            code: 400,
            msg: "错误",
            data: err
        })
        if (result.length == 0) {
            return res.send({
                code: 200,
                msg: '未查询到该用户的核酸数据',
                data: null
            })
        }
        return res.send({
            code: 200,
            msg: "成功",
            data: result
        })
    })
});
module.exports = router